TreeGrid calculations tutorial

Previous <<< Features tutorial Next >>> Editing tutorial
Back to Index


 

These tutorials show using calculated formulas in TreeGrid.

They show using calculated columns, fixed and tree calculated rows and calculation order.

For more information see documentation.

Advanced example (calculated bill) you can see here.

Every example can be used on single page with this structure:

<html>
    <head>
        <script src="../Grid/GridE.js"> </script>
    </head>
    <body>
        <div style="width:100%;height:100%;"> 
           <treegrid Data_Url="CalcX ... .xml"> </treegrid>
        </div>
    </body>
</html>

If you modify in input data some attributes saved to cookies, increase <Cfg> attribute Version to suppress old configuration.

If you have any problem with formula results, you can set <Cfg> attribute DebugCalc to 1 to show all errors in formulas.

  
 
 

Calculated columns

Show example
  • If you want to calculate column's data, you have to set its attribute Formula to expression to calculate.
  • In this expression you can use any JavaScript syntax, operators and even functions.
  • To reference other cells in actual row, you can use column name as variable.
  • Calculated columns cannot be edited by user.
 Show data source 
<Grid>
  <Cfg id='Calc1'/>
  <Cols>
    <C Name='Data' Type='Int'/>
    <C Name='Result' Type='Int' 
       Formula='Data*Data-1'/>
  </Cols>
  <Body>
    <B>
      <I Data='10'/>
      <I Data='-5'/>
      <I Data='0'/>
      <I Data='134'/>
    </B>
  </Body>
</Grid>
  
 
 

Calculated (result) rows

Show example
  • Calculated row must have attribute Calculated='1'. And for every cell you have to set its Formula.
  • In calculated rows are never used formulas from columns. If you want to use the same formula as in column, you need to repeat it to the calculated row's cell.
  • Calculated rows are usually fixed rows or rows with children.
  • In calculated rows you can use aggregate functions to iterate all variable rows or row's children.
  • Cells with formula cannot be edited by user.
 Show data source 
<Grid>
  <Cfg id='Calc2'/>
  <Cols>
    <C Name='Data' Type='Int'/>
    <C Name='Result' Type='Int' 
       Formula='Data*Data-1'/>
  </Cols>
  <Body>
    <B>
      <I Data='10'/>
      <I Data='-5'/>
      <I Data='0'/>
      <I Data='134'/>
    </B>
  </Body>
  <Foot>
    <I Calculated='1' 
       DataFormula='count()' 
       ResultFormula='sum()'/>
    <I Calculated='1' 
       DataFormula='sum()' 
       ResultFormula='Data*Data-1'/>
  </Foot>
</Grid>
  
 
 

Calculation order

Show example
  • If you use in formulas results of other formulas, you need to set calculation order in that will be these formulas calculated.
  • By default there is used alphabetical order, acording to column names (not by column position !).
  • You have to set in every row its attribute CalcOrder to comma separated list of all calculated cells.
  • Any cell not listed in CalcOrder will not be calculated, even it has set Formula.
  • To set the same CalcOrder to more rows you can set it to their default row (<D>). Every row without Def attribute has default row named "R".
 Show data source 
 
<Grid>
  <Cfg id='Calc3'/>
  <Cols>
    <C Name='Data1' Type='Int'/>
    <C Name='Data2' Type='Int'/>
    <C Name='Tmp1' Type='Int' 
       Formula='Data1*Data1+Data2*Data2'/>
    <C Name='Tmp2' Type='Int' 
       Formula='Data1*Data1-Data2*Data2'/>
    <C Name='Result' Type='Float' 
       Formula='Tmp1/Tmp2' Format='0.000'/>
  </Cols>
  <Def> 
    <D Name='R' 
       CalcOrder='Tmp1,Tmp2,Result'/>
  </Def>
  <Body>
    <B>
      <I Data1='10' Data2='6'/>
      <I Data1='-5' Data2='3'/>
      <I Data1='0' Data2='1'/>
      <I Data1='134' Data2='123'/>
    </B>
  </Body>
  <Foot>
    <I Calculated='1' Tmp1Formula='sum()' 
       Tmp2Formula='sum()' 
       Data1Formula='Tmp1*Tmp1+Tmp2*Tmp2' 
       Data2Formula='Tmp1*Tmp1-Tmp2*Tmp2' 
       ResultFormula='Data1/Data2' 
       CalcOrder='Tmp1,Tmp2,Data1,Data2,Result'
       />
  </Foot>
</Grid>
  
 
 

Calculated tree

Show example
  • In tree you can use results in parent rows for its children. This can be used in the same way as calculated fixed rows, see example 2.
  • The aggregate functions iterate all immediate children of the parent.
  • The children are always calculated before their parent.
  • There is often the same setting for many rows, you can use defaults (one default row for all) to shorter data description.
  • If you refer to other cells, you can set row's or cell's attribute Recalc to specify what to recalculate if user changes the input value.
 Show data source 
 
<Grid>
  <Cfg id='Calc4' MainCol='Data'/>
  <Cols>
    <C Name='Data' Type='Int' Width='120'/>
    <C Name='Result' Type='Int' 
	    Formula='Data*Data-1'/>
  </Cols>
  <Def>
    <D Name='Tree' Calculated='1' 
       DataFormula='count()' 
       ResultFormula='sum()' 
       DataFormat='Count=######'/>
  </Def>
  <Body>
    <B>
      <I Def='Tree'>
        <I Data='10'/>
        <I Data='-5'/>
      </I>
      <I Def='Tree'>
        <I Def='Tree'>
          <I Data='0'/>
          <I Data='8'/>
        </I>
        <I Data='5'/>
        <I Data='-1'/>
      </I>
    </B>
  </Body>
  <Foot>
    <I Def='Tree'/>
  </Foot>
</Grid>
  
 
 

Calculation order in tree

Show example
  • By default are row's children calculated before row's cells. You can change this direction by adding '*' to CalcOrder to position, where you want to calculate children.
  • For more complex calculation order you can divide CalcOrder attributes to more parts (CalcOrder,CalcOrder1,CalcOrder2,...) and reference these orders from parent row's CalcOrder by *,*1,*2,...
  • In this example is used this calculation order:
    • First are calculated columns Total and Tmp upstairs (child rows before parent). The tmp column is used only as temporary and should be hidden by setting Visible='0' and CanHide='0'.
    • Next are calculated columns PerParent and PerTotal downstairs (parent before child rows). Because formulas in these columns use calculated result from fixed Root row.
  • Because of complex calculation order, the Recalc attribute of all rows is set to 256 to recalculate the whole grid after any change.
  • If you have more fixed rows and you want to calculate their cells by random, you can use <Cfg> attribute CalcOrder instead of CalcOrder of fixed rows.
 Show data source 
<Grid>
  <Cfg id='Calc4' MainCol='Node'/>
  <Cols>
    <C Name='Node' Type='Text'/>
    <C Name='Active' Type='Bool'/>
    <C Name='Cost' Type='Float'/>
    <C Name='Total' Type='Float'/>
    <C Name='Quantity' Type='Int'/>
    <C Name='Tmp' Type='Float'/>
    <C Name='PerParent' Type='Int' 
       Format='0%'/>
    <C Name='PerTotal' Type='Int' 
       Format='0%'/>
  </Cols>
  <Header Wrap='1' Cost='Local cost' 
     Total='Total cost' 
     Quantity='Total quantity' 
     Tmp='Tmp cost (hidden)' 
     PerParent='% of parent cost' 
     PerTotal='% of total cost'/>
  <Def>
    <D Name='R' Calculated='1' Recalc='256'
       TotalFormula='Cost+sum("Tmp")' 
       TmpFormula='Active*Total*Quantity' 
       CalcOrder='*,Total,Tmp' 
       CalcOrder1='PerParent,PerTotal,*1'
       PerParentFormula='Tmp/Parent.Total'
       PerTotalFormula='PerParent*Parent.PerTotal'
       />
    <D Name='FirstLevel'
      PerParentFormula='Tmp/Root.Total' 
      PerTotalFormula='PerParent'
      />
  </Def>
  <Body>
    <B>
      <I Def='FirstLevel' Node='Level 1' 
        Active='1' Cost='125' Quantity='4'>
        <I Node='Level 1-1' Active='1' 
          Cost='25' Quantity='3'/>
        <I Node='Level 1-2' Active='1' 
          Cost='4' Quantity='4'>
          <I Node='Level 1-2-1' Active='0' 
            Cost='20' Quantity='8'/>
          <I Node='Level 1-2-2' Active='1' 
            Cost='5' Quantity='2'/>
        </I>
        <I Node='Level 1-3' Active='0' 
          Cost='122' Quantity='12'/>
      </I>
      <I Def='FirstLevel'  Node='Level 2' 
        Active='1' Cost='21' Quantity='3'>
        <I Node='Level 2-1' Active='1' 
          Cost='28' Quantity='4'/>
      </I>
    </B>
  </Body>
  <Foot>
    <I id='Root' CalcOrder='*,Total,Tmp,*1' 
      Node='Root' CanEdit='0' 
      ActiveType='Text' CostType='Text' 
      QuantityType='Text' TmpFormula=' ' 
      TmpType='Text' 
      TotalFormula='sum("Tmp")' 
      PerParentType='Text' PerTotal='1'/>
  </Foot>
</Grid>
  

Previous <<< Features tutorial Next >>> Editing tutorial
Back to Index